package com.abel.bigwater.wflow.mapper

import com.abel.bigmeter.service.LocParam
import com.abel.bigwater.model.BwDmaLoc
import org.apache.ibatis.annotations.Insert
import org.apache.ibatis.annotations.Mapper
import org.apache.ibatis.annotations.Select
import org.apache.ibatis.annotations.Update

@Mapper
interface LocMapper {

    @Select("""
        SELECT l.dma_id AS dmaId,
        d.dma_name AS dmaName,
        ST_AsText(l.dma_loc) AS dmaLoc,
        ST_AsText(l.dma_region) AS dmaRegion,
        l.show_type AS showType,
        l.show_days AS showDays,
        dl.stat_date AS lastLossDate,
        dl.parent_phyloss1 AS lastLoss,
        dl.num_seconds AS numSeconds,
        dl.phyloss_rate AS lastLossRate,
        dl.comments AS comments

        FROM bw.bw_dma_loc l LEFT JOIN bw.bw_dma d ON l.dma_id = d.dma_id

        LEFT JOIN bw.bw_dma_loss dl ON l.dma_id = dl.dma_id

        LEFT JOIN (
        SELECT dma_id AS dmaId
        , MAX(stat_date) AS statDate
        FROM bw.bw_dma_loss
        WHERE dma_id = #{dmaId}
        GROUP BY dma_id) dlm
        ON l.dma_id = dlm.dmaId
        LEFT JOIN bw.bw_dma_loss dl ON l.dma_id = dl.dma_id AND (dlm.statDate = dl.stat_date
        OR DATE_SUB(dlm.statDate, INTERVAL 1 DAY) = dl.stat_date)
        """)
    fun selectDmaLocById(lp: LocParam): BwDmaLoc?

    @Select("""<script>
        SELECT l.dma_id AS dmaId,
        d.dma_name AS dmaName,
        ST_AsText(l.dma_loc) AS dmaLoc,
        ST_AsText(l.dma_region) AS dmaRegion,
        l.show_type AS showType,
        l.show_days AS showDays,
        dl.stat_date AS lastLossDate,
        dl.parent_phyloss1 AS lastLoss,
        dl.num_seconds AS numSeconds,
        dl.phyloss_rate AS lastLossRate,
        ds.q_max AS qMax,
        ds.q_min AS qMin,
        ds.q_avg AS qAvg,
        dl.comments AS comments
        FROM bw.bw_dma_loc l JOIN bw.bw_dma d
        ON l.dma_id = d.dma_id
        LEFT JOIN
        (SELECT * FROM bw.bw_dma_loss
        <where>
            <if test="firmId != null">
                firm_id LIKE #{firmId}
            </if>
            <if test="dmaId != null">
                AND dma_id = #{dmaId}
            </if>
            <if test="statDate != null">
                AND stat_date = #{statDate}
            </if>
            <if test="statDate1 != null">
                AND stat_date &gt;= #{statDate1}
            </if>
            <if test="statDate2 != null">
                AND stat_date &lt;= #{statDate2}
            </if>
        </where>
        ) AS dl
        ON l.dma_id = dl.dma_id
        LEFT JOIN (
            SELECT * FROM bw.bw_data_stat
            <where>
	            <if test="firmId != null">
	                firm_id LIKE #{firmId}
	            </if>
	            <if test="dmaId != null">
	                AND dma_id = #{dmaId}
	            </if>
	            <if test="statDate != null">
	                AND stat_date = #{statDate}
	            </if>
	            <if test="statDate1 != null">
	                AND stat_date &gt;= #{statDate1}
	            </if>
	            <if test="statDate2 != null">
	                AND stat_date &lt;= #{statDate2}
	            </if>
            </where>
        ) AS ds ON l.dma_id = ds.dma_id AND dl.stat_date = ds.stat_date
        <where>
            <if test="firmId != null">
                d.firm_id LIKE #{firmId}
            </if>
            <if test="dmaId != null">
                AND d.dma_id = #{dmaId}
            </if>
        </where>
        ORDER BY dl.stat_date DESC
        LIMIT 0, 500
        </script>""")
    fun selectDmaLocAll(lp: LocParam): List<BwDmaLoc>?

    @Select("""<script>
        SELECT l.dma_id AS dmaId,
        d.dma_name AS dmaName,
        ST_AsText(l.dma_loc) AS dmaLoc,
        ST_AsText(l.dma_region) AS dmaRegion,
        l.show_type AS showType,
        l.show_days AS showDays,
        dl.stat_date AS lastLossDate,
        dl.parent_phyloss1 AS lastLoss,
        dl.num_seconds AS numSeconds,
        dl.phyloss_rate AS lastLossRate,
        ds.q_max AS qMax,
        ds.q_min AS qMin,
        ds.q_avg AS qAvg,
        dl.comments AS comments
        FROM bw.bw_dma_loc l JOIN bw.bw_dma d ON l.dma_id = d.dma_id
        LEFT JOIN
        (SELECT dma_id AS dmaId
         , MAX(stat_date) AS statDate
         FROM bw.bw_dma_loss
        <where>
            <if test="firmId != null">
                firm_id LIKE #{firmId}
            </if>
            <if test="dmaId != null">
                AND dma_id = #{dmaId}
            </if>
            <if test="statDate != null">
                AND stat_date = #{statDate}
            </if>
            <if test="statDate1 != null">
                AND stat_date &gt;= #{statDate1}
            </if>
            <if test="statDate2 != null">
                AND stat_date &lt;= #{statDate2}
            </if>
        </where>
        GROUP BY dma_id
        ) AS dlm ON l.dma_id = dlm.dmaId
        LEFT JOIN bw.bw_dma_loss dl ON dlm.dmaId = dl.dma_id AND (dlm.statDate = dl.stat_date
        OR DATE_SUB(dlm.statDate, INTERVAL 1 DAY) = dl.stat_date)
        LEFT JOIN bw.bw_data_stat ds ON dl.dma_id = ds.dma_id
        AND dlm.statDate = ds.stat_date AND dlm.statDate = ds.stat_date
        <where>
            <if test="firmId != null">
                d.firm_id LIKE #{firmId}
            </if>
            <if test="dmaId != null">
                AND d.dma_id = #{dmaId}
            </if>
        </where>
        ORDER BY dl.stat_date DESC
        LIMIT 0, 500
        </script>""")
    fun selectDmaLocAllLast(lp: LocParam): List<BwDmaLoc>?

    @Insert("""
        INSERT INTO bw.bw_dma_loc (dma_id, dma_loc, dma_region
        , create_by
        , create_date)
        VALUES (#{dmaId},
        ST_GeomFromText(#{dmaLoc}),
        ST_GeomFromText(#{dmaRegion})
        , #{createBy}
        , #{createDate})
        """)
    fun insertDmaLoc(dl: BwDmaLoc): Int

    @Update("""<script>
        UPDATE bw.bw_dma_loc
        <set>
            <when test='dmaLoc != null'>
                dma_loc = ST_GeomFromText(#{dmaLoc}),
            </when>
            <when test='dmaRegion != null'>
                dma_region = ST_GeomFromText(#{dmaRegion}),
            </when>
            <when test="updateBy != null">
                update_by = #{updateBy},
            </when>
            <when test="updateDate != null">
                update_date = #{updateDate}
            </when>
        </set>
        WHERE dma_id = #{dmaId}
        </script>
    """)
    fun updateDmaLoc(dl: BwDmaLoc): Int

    @Update("""
        DELETE FROM bw.bw_dma_loc
        WHERE dma_id = #{dmaId}
    """)
    fun deleteDmaLoc(dl: BwDmaLoc): Int
}